<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="${namespace}">

    <!-- 所有列 -->
    <sql id="allColumn">
        ${allColumn}
    </sql>

    <!-- 更新字段 -->
    <sql id="updateColumn">
        <trim  suffixOverrides="," >
${updateBatchColProps}
        </trim>
    </sql>

    <!--Entity字段转SQL字段-->
    <sql id="entityColumnToSQL">
${needColumnCondition}
    </sql>

    <!-- 批量新增 -->
    <insert id="batchAdd" parameterType="Object" >
        insert into ${tableName}
        (${addProperty})
        values
        <foreach collection="entityList" index="index" item="item" separator=",">
            (${batchAddValue})
        </foreach>
    </insert>

        <!-- ******************************************************* 以上新增字段自动生成后可以直接替换 ******************************************************* -->

    <!-- 批量更新 -->
    <update id="batchUpdate" parameterType="Object">
        <foreach collection="entityList" separator=";" item="item">
            update ${tableName} set
            <include refid="updateColumn" />
            where id = <#noparse>#{item.id}</#noparse>
            and member_id = <#noparse>#{memberInfo.memberId}</#noparse>
        </foreach>
    </update>

    <!-- 批量更新 - 重复数据!-->
    <update id="batchUpdateSingle" parameterType="Object">
        update ${tableName} set
        <include refid="updateColumn" />
        where member_id = <#noparse>#{memberInfo.memberId}</#noparse>
        and id in
        <foreach collection="ids" item="item" open="(" separator="," close=")">
            <#noparse>#{item}</#noparse>
        </foreach>
    </update>

    <!-- 批量删除 -->
    <delete id="batchDelete" parameterType="Object">
        delete from ${tableName}
        where member_id = <#noparse>#{memberInfo.memberId}</#noparse>
        and id in
        <foreach collection="ids" item="item" open="(" separator="," close=")">
            <#noparse>#{item}</#noparse>
        </foreach>
    </delete>

    <!-- 批量逻辑删除 -->
    <delete id="batchLogicDelete" parameterType="Object">
        delete from ${tableName}
        where member_id = <#noparse>#{memberInfo.memberId}</#noparse>
        and id in
        <foreach collection="ids" item="item" open="(" separator="," close=")">
            <#noparse>#{item}</#noparse>
        </foreach>
    </delete>

    <!-- 批量查询 -->
    <select id="list" resultType="${packageStr}.${entityName}">
        select
        <include refid="allColumn" />
        from ${tableName}
        where member_id = <#noparse>#{memberInfo.memberId}</#noparse>
        and id in
        <foreach collection="ids" item="item" open="(" separator="," close=")">
            <#noparse>#{item}</#noparse>
        </foreach>
    </select>

    <!-- 批量条件查询 -->
    <select id="listByFilter" resultType="${packageStr}.${entityName}">
        select
        <!-- 按需获取字段 -->
        <choose>
            <when test="filter.cols != null and filter.cols.length > 0">
                <include refid="needColumn"/>
            </when>
            <otherwise>
                <include refid="allColumn"/>
            </otherwise>
        </choose>
        from ${tableName}
        where member_id = <#noparse>#{memberInfo.memberId}</#noparse>
        <!--查询条件-->
        <include refid="mainFilter"/>
        <!--排序条件 - 比较复杂，能不要就不要-->
        <include refid="orderBy" />
        <!--分页信息 -->
        <choose>
            <when test="filter.offset != null and filter.pageSize != null">
                limit <#noparse>#{filter.offset},#{filter.pageSize}</#noparse>
            </when>
             <otherwise>
                limit 0,10000
            </otherwise>
        </choose>
    </select>

    <!-- 批量条件统计 -->
    <select id="countByFilter" resultType="Integer">
        select
        count(*)
        from ${tableName}
        where member_id = <#noparse>#{memberInfo.memberId}</#noparse>
        <!--查询条件-->
        <include refid="mainFilter"/>
    </select>

    <!-- 按需获取字段 - 判断 -->
    <sql id="needColumn">
        <trim suffixOverrides=",">
            <foreach collection="filter.cols" item="item">
                <include refid="entityColumnToSQL"/>
            </foreach>
        </trim>
    </sql>

    <!-- 排序条件 -->
    <sql id="orderBy">
        <if test="filter.sortFields != null and filter.sortFields.length > 0">
            order by
            <trim suffixOverrides=",">
                <foreach collection="filter.sortFields" item="item">
                    <trim suffixOverrides=",">
                        <include refid="entityColumnToSQL"/>
                    </trim>
                    <choose>
                        <when test="filter.sortOrder == 'DESC'.toString()">
                            DESC,
                        </when>
                        <otherwise>
                            ASC,
                        </otherwise>
                    </choose>

                </foreach>
            </trim>
        </if>
    </sql>

    <!-- 主页面过滤条件 -->
    <sql id="mainFilter">
        <!-- 查询条件 -->
${findListCondition}
    </sql>

    <!-- 主页面查询 -->
    <select id="listMain" resultType="${filterPackageStr}.${resultName}">
        select
        <!-- 按需获取字段 -->
        <choose>
            <when test="filter.cols != null and filter.cols.length > 0">
                <include refid="needColumn"/>
            </when>
            <otherwise>
                <include refid="allColumn"/>
            </otherwise>
        </choose>
        from ${tableName}
        where member_id = <#noparse>#{memberInfo.memberId}</#noparse>
        <include refid="mainFilter"/>
        order by gmt_create desc
        limit <#noparse>#{pageInfo.offset},#{pageInfo.pageSize}</#noparse>
    </select>

    <!-- 主页面查询count -->
    <select id="listCount" resultType="Integer">
        select
        count(*)
        from ${tableName}
        where member_id = <#noparse>#{memberInfo.memberId}</#noparse>
        <include refid="mainFilter"/>
    </select>

    <!-- 获取最大的no -->
    <select id="getMaxNo" resultType="Integer">
        select
        max(no)
        from ${tableName}
    </select>

   <!-- 统计不同字段的数量 -->
   <select id="listFieldCount" resultType="com.differ.jackyun.frame.business.dto.left.LeftTypeCountDTO">
        select
        CONCAT(
        <trim suffixOverrides=", ',' ,">
            <foreach collection="fields" item="item" separator=", ',' ,">
                IFNULL(<include refid="entityColumnToSQL"/> 'null')
            </foreach>
        </trim>
        )
        as type ,
        count(*) as count
        from ${tableName}
        where member_id = <#noparse>#{memberInfo.memberId}</#noparse>
        <!--查询条件-->
        <include refid="mainFilter"/>
        group by
        <trim suffixOverrides=",">
            <foreach collection="fields" item="item">
                <include refid="entityColumnToSQL"/>
            </foreach>
        </trim>
   </select>

</mapper>